package cn.oa.dao.impl;

import cn.oa.dao.AttendanceDao;
import cn.oa.dao.BaseDao;
import cn.oa.entity.Attendance;
import cn.oa.util.DatabaseUtil;
import cn.oa.util.DateUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class AttendanceDaoImpl extends BaseDao implements AttendanceDao {
    public AttendanceDaoImpl(Connection conn) {
        super(conn);
    }


    @Override
    public int deleteAttendanceByEmployeeId(int employeeId) throws SQLException {
        String sql = "delete from attendance where employeeId=?";
        int result = 0;
        try{
            result = this.executeUpdate(sql, employeeId);
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
        return result;
    }

    @Override
    public Attendance getRecordByEmployeeIdAndDate(int employeeId, String date) throws SQLException {
        Attendance attendance = null;
        String sql = "select * from attendance where employeeId = ? and DATEDIFF(?, signInTime)=0";
        ResultSet rs = null;
        try{
            rs = this.executeQuery(sql,employeeId,date);
            while (rs.next()){
                attendance = new Attendance();
                attendance.setId(rs.getInt("id"));
                attendance.setSignInTime(rs.getTimestamp("signInTime"));
                attendance.setSignOutTime(rs.getTimestamp("signOutTime"));
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return attendance;
    }

    @Override
    public int addSinginByEmployeeId(int id) throws SQLException {
        int result = 0;
        String now = DateUtil.now();
        String sql = "insert into attendance (employeeId, signInTime) values (?, ?)";
        try{
            result = this.executeUpdate(sql,id,now);
        }catch(SQLException e){
            e.printStackTrace();
            throw e;
        }
        return result;
    }

    @Override
    public int updateSingoutByAttendanceId(int id) throws SQLException {
        int result = 0;
        String now = DateUtil.now();
        String sql = "update attendance set signOutTime = ? where id = ?";
        try{
            result = this.executeUpdate(sql,now,id);
        }catch(SQLException e){
            e.printStackTrace();
            throw e;
        }
        return result;
    }

    @Override
    public List<Attendance> getAttendancesByPage(String name, String searchDate, int pageNo, int pageSize) throws SQLException {
        List<Attendance> list = new ArrayList<Attendance>();
        ResultSet rs = null;
        String sql = "select a.id, e.name, e.employeeNo, a.signInTime, a.signOutTime from attendance a " +
                "left join employee e on a.employeeId = e.id ";

        if((name!=null && !name.equals("")) && searchDate!=null && !searchDate.equals("")){
            sql += " where e.name like '%"+name+"%' and DATEDIFF(?, a.signInTime)=0 ";
        }else{
            if(name!=null && !name.equals("")){
                sql += " where e.name like '%"+name+"%' ";
            }else if(searchDate!=null && !searchDate.equals("")){
                sql += " where DATEDIFF(?, a.signInTime)=0 ";
            }
        }
        sql += " LIMIT ?,? ";
        try{
            if(searchDate!=null && !searchDate.equals("")){
                rs = this.executeQuery(sql, searchDate,(pageNo - 1) * pageSize,pageSize);
            }else{
                rs = this.executeQuery(sql, (pageNo - 1) * pageSize,pageSize);
            }
            while(rs.next()){
                Attendance attendance = new Attendance();
                attendance.setId(rs.getInt("id"));
                attendance.getEmployee().setName(rs.getString("name"));
                attendance.getEmployee().setEmployeeNo(rs.getString("employeeNo"));
                attendance.setSignInTime(rs.getTimestamp("signInTime"));
                attendance.setSignOutTime(rs.getTimestamp("signOutTime"));
                list.add(attendance);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return list;
    }

    @Override
    public int getAttendancesCount(String name, String searchDate) throws SQLException {
        int count = 0;
        ResultSet rs = null;
        String sql = "select count(*) from attendance a left join employee e on a.employeeId = e.id ";
        if((name!=null && !name.equals("")) && (searchDate!=null && !searchDate.equals("") )){
            sql += " where e.name like '%"+name+"%' and DATEDIFF(?, a.signInTime)=0 ";
        }else{
            if(name!=null && !name.equals("")){
                sql += " where e.name like '%"+name+"%' ";
            }else if(searchDate!=null && !searchDate.equals("")){
                sql += " where DATEDIFF(?, a.signInTime)=0 ";
            }
        }
        try{
            if(searchDate!=null && !searchDate.equals("")){
                rs = this.executeQuery(sql,searchDate);;
            }else{
                rs = this.executeQuery(sql);
            }
            while(rs.next()){
                count = rs.getInt("count(*)");
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return count;
    }
}
